package yxy;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TongJi {

	private static String getCellvalue(Row row, int i) {
		Cell cell = row.getCell(i);
		if (cell == null) {
			return "";
		} else {
			if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
				return String.valueOf((int) cell.getNumericCellValue());
			}
			try {
				return cell.getStringCellValue().trim();
			} catch (Exception e) {
				e.printStackTrace();
				return "";
			}
		}
	}

	public static void main(String[] args) throws IOException {
		File f = new File("C:\\Users\\Administrator\\Desktop\\excel处理\\保存率.xlsx");
		XSSFWorkbook xwb = new XSSFWorkbook(f.getAbsolutePath());

		int n = xwb.getNumberOfSheets();
		Map<String, Map<String, List<Integer>>> resMap = new LinkedHashMap<>();
		for (int i = 0; i < n; i++) {
			Sheet sheet = xwb.getSheetAt(i);
			String sheetName = sheet.getSheetName();
			System.out.println(sheetName);
			Map<String, List<Integer>> pinzhongMap = new LinkedHashMap<>();
			resMap.put(sheetName, pinzhongMap);

			for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {// getLastRowNum，获取最后一行的行标
				Row row = sheet.getRow(j);
				if (row != null) {
					String family = getCellvalue(row, 1);
					List<Integer> huoAndZhong = pinzhongMap.get(family);
					if (huoAndZhong == null) {
						huoAndZhong = new ArrayList<>();
						huoAndZhong.add(1);
						huoAndZhong.add(1);

						pinzhongMap.put(family, huoAndZhong);
					} else {
						Integer zong = huoAndZhong.get(1);
						Integer huo = huoAndZhong.get(0);
						huoAndZhong.set(1, zong + 1);
						if (!"死".equals(StringUtils.deleteWhitespace(getCellvalue(row, 2)))) {
							huoAndZhong.set(0, huo + 1);
						}
					}

				}
			}

		}

		System.out.println(resMap);

		File f2 = new File("C:\\Users\\Administrator\\Desktop\\excel处理\\保存结果.xlsx");
		XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(f2));
		Sheet sheet = workbook.getSheetAt(0);
        
        int i = 0;
        for (String year : resMap.keySet()) {
			Map<String, List<Integer>> yearMap = resMap.get(year);
			Row row = sheet.createRow(i);
			
			Cell cell = row.createCell(0);
			cell.setCellValue(year);
			i++;
			
			for (String pingzhong : yearMap.keySet()) {
				Row row2 = sheet.createRow(i);
				Cell pingZhongCell = row2.createCell(0);
				pingZhongCell.setCellValue(pingzhong);
				
				List<Integer> list = yearMap.get(pingzhong);
				for (int j = 0; j < list.size(); j++) {
					Cell createCell = row2.createCell(j+1);
					createCell.setCellValue(list.get(j));
				}
				
						
				i++;
			}
			
			
		}
        
        workbook.write(new FileOutputStream(f2));
       /* for(int i=0;i<datas.size();i++){
			rowList = datas.get(i);
			row = sheet.createRow(rowIndex++);
			
			// 遍历某一行的结果
			for (int n = 0; n < rowList.size(); n++) {
				// 使用行创建列对象
				Cell cell = row.createCell(n);
				cell.setCellStyle(style2);
				if (rowList.get(n) != null) {
					cell.setCellValue((String) rowList.get(n).toString());
				} else {
					cell.setCellValue("");
				}
			}
			
			//sheet写满新增sheet
			if (sheet.getLastRowNum() == SHEET_TOTAL) {
				sheetCount ++;
				newSheetName = oldSheetName + sheetCount;
				sheet = workbook.createSheet(newSheetName);
				Map<Integer, Integer> autoWidthMap = this.autoSetColumnWidth(header, datas);
				this.createTitle(sheet, header,autoWidthMap, this.createStyle1(workbook), 0);
				rowIndex = 1;
			}
		}*/
        

	}

}
